package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;


public class DBGunReplicas implements IFDBGeneric<GunReplicas>{
    private  Connection con;
   
    public DBGunReplicas() {
      con = DbConnection.getInstance().getDBcon();
    }
    

    public ArrayList<GunReplicas> getAll(){
        return miscWhere("");
    }
    
    public GunReplicas find(String name){  
        return singleWhere("name = '"+name);
    }
    
    public GunReplicas find_byID(int Prid){  
        return singleWhere("Prid = '"+Prid);
    }
   
    
    public int insert(GunReplicas gr) throws Exception
    {  
  
       int rc = -1;
	   String query="INSERT INTO Product(name, PurchasePrice, SalesPrice, RentPrice, CountryOfOrigin, CurrentStock, MinStock, Sid)  VALUES('"+
	  		gr.getName()  + "',"  +
		    gr.getPurchasePrice()  + ","  +
            gr.getSalesPrice() + "," +
            gr.getRentPrice() + ",'" +
            gr.getCountryOfOrigin() + "',"+ gr.getCurrentStock() + ","+ gr.getMinStock() + ","+gr.getSid() + ")" ;
	   
	   		


      try{
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
     	  ResultSet rs = stmt.getGeneratedKeys();
     	  if (rs.next()) rc = rs.getInt(1);
     	
     			  
     	String query1 = "INSERT INTO GunReplicas(Prid, fabric, caliber) VALUES("+rc+","
     			+ " '"+gr.getFabric()+"',"+gr.getCaliber()+")";
     	stmt.executeUpdate(query1);
          stmt.close();
      }
       catch(SQLException ex){
          System.err.println("Could not insert GunReplicas obj with name "+gr.getName());
       }
       return(rc);
    }

    
	public int update(GunReplicas gr)
	{
		GunReplicas grObj  = gr;
		int rc=-1;

		String query="UPDATE Product SET "+
		 	  "name ='"+ grObj.getName()+"', "+
		 	  "purchasePrice ="+ grObj.getPurchasePrice() + ", " +
              "salesPrice ="+ grObj.getSalesPrice() + ", " +
              "rentPrice ="+ grObj.getRentPrice() + ", " +
              "countryOfOrigin ='"+ grObj.getCountryOfOrigin() + "', " +
              "currentStock ="+ grObj.getCurrentStock() + ", " +
              "minStock ="+ grObj.getMinStock() + " " +
		          " WHERE Prid = "+ grObj.getPrid() + ""; 
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		System.out.println(query);
	 	 	rc = stmt.executeUpdate(query);
	 	 	
	 	String query1="UPDATE GunReplicas SET "+
	 			"fabric ='"+ grObj.getFabric()+"',"+
	 			"caliber = "+ grObj.getCaliber()+" "+
	 				"WHERE Prid = "+grObj.getPrid() + "";
	 		stmt.executeUpdate(query1);
	 	 	
	 	 	
	 	 	

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in GunReplicas db: "+ex);
	  	}
		return(rc);
	}
	
	
	public int delete(int Prid)
	{
        int rc=-1;
	  
	  	String query="DELETE FROM product WHERE Prid = '" +Prid + "'";
       
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in GunReplicas db: "+ex);
   	        }
		return(rc);
	}
	

	 
	private ArrayList<GunReplicas> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<GunReplicas> list = new ArrayList<GunReplicas>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the product from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			GunReplicas grObj = new GunReplicas();
	     	 grObj = buildGunReplicas(results);	
                 list.add(grObj);	
		}
   
			
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one product 	
	private GunReplicas singleWhere(String wClause)
	{
		ResultSet results;
		GunReplicas grObj = new GunReplicas();
                
	        String query =  buildQuery(wClause);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            grObj = buildGunReplicas(results);
                            //assocaition is to be build
                            stmt.close();
                           
			}
                        else{ //no product was found
                            grObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return grObj;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT Prid, name, purchasePrice,SalesPrice, rentPrice, CountryOfOrigin,currentStock, minStock, Sid, fabric, caliber FROM Clothing c, Product p WHERE c.Prid=p.Prid";
		
		if (wClause.length()>0)
			query=query+" AND "+ wClause;
			
		return query;
	}
	
	private GunReplicas buildGunReplicas(ResultSet results)
      {   GunReplicas grObj = new GunReplicas();
          try{ // the columns from the table product  are used
        	  	grObj.setPrid(results.getInt("Prid"));
                grObj.setName(results.getString("name"));
                grObj.setPurchasePrice(results.getDouble("purchasePrice"));
                grObj.setSalesPrice(results.getDouble("salesPrice"));
                grObj.setRentPrice(results.getDouble("rentPrice"));
                grObj.setCountryOfOrigin(results.getString("CountryOfOrigin"));
                grObj.setCurrentStock(results.getInt("currentStock"));
                grObj.setMinStock(results.getInt("minStock"));
                grObj.setSid(results.getInt("Sid"));
                grObj.setFabric(results.getString("fabric"));
                grObj.setCaliber(results.getDouble("caliber"));

          }
         catch(Exception e)
         {
             System.out.println("error in building the GunReplicas object");
         }
         return grObj;
      }

 
}  
    

